﻿REVOKE ALL ON Activity FROM [LaborReader]
REVOKE ALL ON BatchCompilation FROM [LaborReader]
REVOKE ALL ON Batches FROM [LaborReader]
REVOKE ALL ON EnzymeCode FROM [LaborReader]
REVOKE ALL ON Experiments FROM [LaborReader]
REVOKE ALL ON FreshWeights FROM [LaborReader]
REVOKE ALL ON Material FROM [LaborReader]
REVOKE ALL ON Parameter FROM [LaborReader]
REVOKE ALL ON ProcessedData FROM [LaborReader]
REVOKE ALL ON Protocol FROM [LaborReader]
REVOKE ALL ON ProtocolSetting FROM [LaborReader]
REVOKE ALL ON RawData FROM [LaborReader]
REVOKE ALL ON Unit FROM [LaborReader]

REVOKE ALL ON ActivityView FROM [LaborReader]
REVOKE ALL ON AllAssaysControlValues FROM [LaborReader]
REVOKE ALL ON BatchCompilationView FROM [LaborReader]
REVOKE ALL ON BatchView FROM [LaborReader]
REVOKE ALL ON EnzymeView FROM [LaborReader]
REVOKE ALL ON ExperimentView FROM [LaborReader]
REVOKE ALL ON FreshWeightView FROM [LaborReader]
REVOKE ALL ON MaterialView FROM [LaborReader]
REVOKE ALL ON ParameterView FROM [LaborReader]
REVOKE ALL ON ProtocolSettingView FROM [LaborReader]
REVOKE ALL ON ProtocolView FROM [LaborReader]

GO
GRANT SELECT ON Activity TO [LaborReader]
GRANT SELECT ON BatchCompilation TO [LaborReader]
GRANT SELECT ON Batches TO [LaborReader]
GRANT SELECT ON EnzymeCode TO [LaborReader]
GRANT SELECT ON Experiments TO [LaborReader]
GRANT SELECT ON FreshWeights TO [LaborReader]
GRANT SELECT ON Material TO [LaborReader]
GRANT SELECT ON Parameter TO [LaborReader]
GRANT SELECT ON ProcessedData TO [LaborReader]
GRANT SELECT ON Protocol TO [LaborReader]
GRANT SELECT ON ProtocolSetting TO [LaborReader]
GRANT SELECT ON RawData TO [LaborReader]
GRANT SELECT ON Unit TO [LaborReader]

GRANT SELECT ON ActivityView TO [LaborReader]
GRANT SELECT ON AllAssaysControlValues TO [LaborReader]
GRANT SELECT ON BatchCompilationView TO [LaborReader]
GRANT SELECT ON BatchView TO [LaborReader]
GRANT SELECT ON EnzymeView TO [LaborReader]
GRANT SELECT ON ExperimentView TO [LaborReader]
GRANT SELECT ON FreshWeightView TO [LaborReader]
GRANT SELECT ON MaterialView TO [LaborReader]
GRANT SELECT ON ParameterView TO [LaborReader]
GRANT SELECT ON ProtocolSettingView TO [LaborReader]
GRANT SELECT ON ProtocolView TO [LaborReader]



--ab hier!
CREATE LOGIN [MPIMP-GOLM\Vosloh] FROM WINDOWS WITH DEFAULT_DATABASE=[Plato], DEFAULT_LANGUAGE=[Deutsch]
CREATE LOGIN [MPIMP-GOLM\Tschoep] FROM WINDOWS WITH DEFAULT_DATABASE=[Plato], DEFAULT_LANGUAGE=[us_english]
CREATE LOGIN [MPIMP-GOLM\Pyl] FROM WINDOWS WITH DEFAULT_DATABASE=[Plato], DEFAULT_LANGUAGE=[Deutsch]
CREATE LOGIN [MPIMP-GOLM\Hoehne] FROM WINDOWS WITH DEFAULT_DATABASE=[Plato], DEFAULT_LANGUAGE=[us_english]
CREATE LOGIN [MPIMP-GOLM\Haedrich] FROM WINDOWS WITH DEFAULT_DATABASE=[Plato], DEFAULT_LANGUAGE=[us_english]
CREATE LOGIN [MPIMP-GOLM\Guenther] FROM WINDOWS WITH DEFAULT_DATABASE=[Plato], DEFAULT_LANGUAGE=[Deutsch]
CREATE LOGIN [MPIMP-GOLM\Durand] FROM WINDOWS WITH DEFAULT_DATABASE=[Plato], DEFAULT_LANGUAGE=[us_english]
CREATE LOGIN [MPIMP-GOLM\Barz] FROM WINDOWS WITH DEFAULT_DATABASE=[Plato], DEFAULT_LANGUAGE=[Deutsch]

CREATE LOGIN [LabDesigner] WITH PASSWORD=N'agpase', DEFAULT_DATABASE=[Plato], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN [LabAdmin] WITH PASSWORD=N'glucose', DEFAULT_DATABASE=[Plato], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN [Lab] WITH PASSWORD=N'enzyme', DEFAULT_DATABASE=[Plato], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE USER [LabDesigner] FOR LOGIN [LabDesigner] WITH DEFAULT_SCHEMA=[dbo]
CREATE USER [LabAdmin] FOR LOGIN [LabAdmin] WITH DEFAULT_SCHEMA=[dbo]
CREATE USER [Lab] FOR LOGIN [Lab] WITH DEFAULT_SCHEMA=[dbo]

USE [Plato]
CREATE USER [MPIMP-GOLM\Vosloh] FOR LOGIN [MPIMP-GOLM\Vosloh] WITH DEFAULT_SCHEMA=[dbo]
CREATE USER [MPIMP-GOLM\Tschoep] FOR LOGIN [MPIMP-GOLM\Tschoep] WITH DEFAULT_SCHEMA=[dbo]
CREATE USER [MPIMP-GOLM\Pyl] FOR LOGIN [MPIMP-GOLM\Pyl] WITH DEFAULT_SCHEMA=[dbo]
CREATE USER [MPIMP-GOLM\Hoehne] FOR LOGIN [MPIMP-GOLM\Hoehne] WITH DEFAULT_SCHEMA=[dbo]
CREATE USER [MPIMP-GOLM\Haedrich] FOR LOGIN [MPIMP-GOLM\Haedrich] WITH DEFAULT_SCHEMA=[dbo]
CREATE USER [MPIMP-GOLM\Guenther] FOR LOGIN [MPIMP-GOLM\Guenther] WITH DEFAULT_SCHEMA=[dbo]
CREATE USER [MPIMP-GOLM\Durand] FOR LOGIN [MPIMP-GOLM\Durand] WITH DEFAULT_SCHEMA=[dbo]
CREATE USER [MPIMP-GOLM\Barz] FOR LOGIN [MPIMP-GOLM\Barz] WITH DEFAULT_SCHEMA=[dbo]


CREATE ROLE [SP_Executor]
CREATE ROLE [LaborUser]
CREATE ROLE [LaborReader]
CREATE ROLE [LaborDesigner]
CREATE ROLE [LaborAdmin]
ALTER ROLE [LaborAdmin] ADD MEMBER [LabAdmin]


ALTER ROLE [LaborDesigner] ADD MEMBER [MPIMP-GOLM\Vosloh]
ALTER ROLE [LaborDesigner] ADD MEMBER [MPIMP-GOLM\Tschoep]
ALTER ROLE [LaborDesigner] ADD MEMBER [MPIMP-GOLM\Pyl]
ALTER ROLE [LaborDesigner] ADD MEMBER [MPIMP-GOLM\Hoehne]
ALTER ROLE [LaborDesigner] ADD MEMBER [MPIMP-GOLM\Haedrich]
ALTER ROLE [LaborDesigner] ADD MEMBER [MPIMP-GOLM\Guenther]
ALTER ROLE [LaborDesigner] ADD MEMBER [MPIMP-GOLM\Durand]
ALTER ROLE [LaborDesigner] ADD MEMBER [LabDesigner]
ALTER ROLE [LaborDesigner] ADD MEMBER [LaborAdmin]


ALTER ROLE [LaborReader] ADD MEMBER [MPIMP-GOLM\Barz]
ALTER ROLE [LaborReader] ADD MEMBER [LaborAdmin]
ALTER ROLE [LaborReader] ADD MEMBER [LaborDesigner]
ALTER ROLE [LaborReader] ADD MEMBER [Lab]
ALTER ROLE [SP_Executor] ADD MEMBER [LaborReader]



GRANT CONNECT TO [Lab] AS [dbo]
GRANT CONNECT TO [LaborAdmin] AS [dbo]
GRANT CONNECT TO [LaborDesigner] AS [dbo]

GRANT CONNECT TO [MPIMP-GOLM\Barz] AS [dbo]
GRANT CONNECT TO [MPIMP-GOLM\Durand] AS [dbo]
GRANT CONNECT TO [MPIMP-GOLM\Guenther] AS [dbo]
GRANT CONNECT TO [MPIMP-GOLM\Haedrich] AS [dbo]
GRANT CONNECT TO [MPIMP-GOLM\Hoehne] AS [dbo]
GRANT CONNECT TO [MPIMP-GOLM\Pyl] AS [dbo]
GRANT CONNECT TO [MPIMP-GOLM\Tschoep] AS [dbo]
GRANT CONNECT TO [MPIMP-GOLM\Vosloh] AS [dbo]


GRANT EXECUTE ON [dbo].[AvailableAnalytes] TO [SP_Executor] AS [dbo]
GRANT EXECUTE ON [dbo].[BatchMaxAvg] TO [SP_Executor] AS [dbo]
GRANT EXECUTE ON [dbo].[InsertEnzyme] TO [SP_Executor] AS [dbo]
GRANT EXECUTE ON [dbo].[ProcessedSampleData] TO [SP_Executor] AS [dbo]
GRANT EXECUTE ON [dbo].[SelectEnzymeActivity] TO [SP_Executor] AS [dbo]
GRANT EXECUTE ON [dbo].[SelectPersons] TO [SP_Executor] AS [dbo]
GRANT EXECUTE ON [dbo].[SelectProcessedData2] TO [SP_Executor] AS [dbo]
GRANT EXECUTE ON [dbo].[UpdateEnzymeActivity] TO [SP_Executor] AS [dbo]
GRANT EXECUTE ON [dbo].[BatchRawDataAvg] TO [LaborDesigner] AS [dbo]

GRANT DELETE ON [dbo].[Activity] TO [LaborAdmin] AS [dbo]
GRANT INSERT ON [dbo].[Activity] TO [LaborAdmin] AS [dbo]
GRANT UPDATE ON [dbo].[Activity] TO [LaborAdmin] AS [dbo]
GRANT SELECT ON [dbo].[Activity] TO [LaborReader] AS [dbo]

GRANT DELETE ON [dbo].[BatchCompilation] TO [LaborDesigner] AS [dbo]
GRANT INSERT ON [dbo].[BatchCompilation] TO [LaborDesigner] AS [dbo]
GRANT UPDATE ON [dbo].[BatchCompilation] TO [LaborDesigner] AS [dbo]
GRANT SELECT ON [dbo].[BatchCompilation] TO [LaborReader] AS [dbo]

GRANT DELETE ON [dbo].[Batches] TO [LaborDesigner] AS [dbo]
GRANT INSERT ON [dbo].[Batches] TO [LaborDesigner] AS [dbo]
GRANT UPDATE ON [dbo].[Batches] TO [LaborDesigner] AS [dbo]
GRANT SELECT ON [dbo].[Batches] TO [LaborReader] AS [dbo]

GRANT DELETE ON [dbo].[EnzymeCode] TO [LaborAdmin] AS [dbo]
GRANT INSERT ON [dbo].[EnzymeCode] TO [LaborAdmin] AS [dbo]
GRANT UPDATE ON [dbo].[EnzymeCode] TO [LaborAdmin] AS [dbo]
GRANT SELECT ON [dbo].[EnzymeCode] TO [LaborReader] AS [dbo]

GRANT DELETE ON [dbo].[Experiments] TO [LaborAdmin] AS [dbo]
GRANT INSERT ON [dbo].[Experiments] TO [LaborDesigner] AS [dbo]
GRANT UPDATE ON [dbo].[Experiments] TO [LaborDesigner] AS [dbo]
GRANT SELECT ON [dbo].[Experiments] TO [LaborReader] AS [dbo]

GRANT DELETE ON [dbo].[FreshWeights] TO [LaborAdmin] AS [dbo]
GRANT INSERT ON [dbo].[FreshWeights] TO [LaborDesigner] AS [dbo]
GRANT UPDATE ON [dbo].[FreshWeights] TO [LaborDesigner] AS [dbo]
GRANT SELECT ON [dbo].[FreshWeights] TO [LaborReader] AS [dbo]

GRANT DELETE ON [dbo].[Material] TO [LaborAdmin] AS [dbo]
GRANT INSERT ON [dbo].[Material] TO [LaborAdmin] AS [dbo]
GRANT UPDATE ON [dbo].[Material] TO [LaborAdmin] AS [dbo]
GRANT SELECT ON [dbo].[Material] TO [LaborReader] AS [dbo]

GRANT DELETE ON [dbo].[ProcessedData] TO [LaborDesigner] AS [dbo]
GRANT INSERT ON [dbo].[ProcessedData] TO [LaborDesigner] AS [dbo]
GRANT UPDATE ON [dbo].[ProcessedData] TO [LaborDesigner] AS [dbo]
GRANT SELECT ON [dbo].[ProcessedData] TO [LaborReader] AS [dbo]

GRANT DELETE ON [dbo].[RawData] TO [LaborDesigner] AS [dbo]
GRANT INSERT ON [dbo].[RawData] TO [LaborDesigner] AS [dbo]
GRANT UPDATE ON [dbo].[RawData] TO [LaborDesigner] AS [dbo]
GRANT SELECT ON [dbo].[RawData] TO [LaborReader] AS [dbo]

GRANT DELETE ON [dbo].[Protocol] TO [LaborDesigner] AS [dbo]
GRANT INSERT ON [dbo].[Protocol] TO [LaborDesigner] AS [dbo]
GRANT UPDATE ON [dbo].[Protocol] TO [LaborDesigner] AS [dbo]
GRANT SELECT ON [dbo].[Protocol] TO [LaborReader] AS [dbo]

GRANT DELETE ON [dbo].[ProtocolSetting] TO [LaborDesigner] AS [dbo]
GRANT INSERT ON [dbo].[ProtocolSetting] TO [LaborDesigner] AS [dbo]
GRANT UPDATE ON [dbo].[ProtocolSetting] TO [LaborDesigner] AS [dbo]
GRANT SELECT ON [dbo].[ProtocolSetting] TO [LaborReader] AS [dbo]

GRANT DELETE ON [dbo].[Parameter] TO [LaborDesigner] AS [dbo]
GRANT INSERT ON [dbo].[Parameter] TO [LaborDesigner] AS [dbo]
GRANT UPDATE ON [dbo].[Parameter] TO [LaborDesigner] AS [dbo]
GRANT SELECT ON [dbo].[Parameter] TO [LaborReader] AS [dbo]




GRANT SELECT ON [dbo].[AllAssaysControlValues] TO [LaborReader] AS [dbo]
